package databaseproject;

import java.sql.*;
import java.util.Scanner;
import java.util.Date;

/**
 *
 * @author jesseday
 */
public class Fantasy {
    private static Connection con;
    private static String username;
    private static boolean userIsAdmin;

    
    public static void doMenu(Connection nCon, String nUsername, boolean nUserIsAdmin)
    {
        con = nCon;
        username = nUsername;
        userIsAdmin = nUserIsAdmin;
        
        boolean done = false;

        while (!done)
        {
            Menu menu = new Menu();
            menu.setTitle("Fantasy Shows");
            menu.addOption("List All Fantasy Shows");
            if (!userIsAdmin)
            {
                menu.addOption("List My Fantasy Shows");
                menu.addOption("Create Fantasy Show");
                menu.addOption("Best Prediction");

            }
            switch (menu.display())
            {
                case (1):
                    Menu listMenu = new Menu();
                    listMenu.addTwoOptionsFromSQLQuery(con, "select Username, "
                            + "Creation_date from Fantasy_show");
                    int choice = listMenu.display();
                    if(choice<listMenu.getBackOption())
                    {
                        displayDetail(listMenu.getOptionAt(choice));
                    }
                    break;
                    
                case (2):
                    if (userIsAdmin)
                    {
                        done = true;
                        break;
                    }
                    Menu alistMenu = new Menu();
                    alistMenu.setTitle(username + "'s Fantasy Shows (Choose to View):");
                    alistMenu.addTwoOptionsFromSQLQuery(con, "select Username, "
                            + "Creation_date from Fantasy_show where username "
                            + "= '"+username+"'");
                    
                    
                    alistMenu.addOption("Delete a Fantasy Show");
                    
                    int achoice = alistMenu.display();
                    
                    if(achoice==alistMenu.getBackOption()-1)
                    {
                        Menu delShow = new Menu();
                        delShow.setTitle("Select Show to Delete:");
                        delShow.addTwoOptionsFromSQLQuery(con, "select Username, "
                                + "Creation_date from Fantasy_show where username "
                                + "= '"+username+"'");
                        int del = delShow.display();
                        
                        if(del<delShow.getBackOption())
                        {
                            String show = delShow.getOptionAt(del);
                            String[] tokens = show.split(" ");
                            String uname = tokens[0];
                            String date = tokens[1]+ " " + tokens[2];
                            try{
                                PreparedStatement st = con.prepareStatement("delete from FANTASY_SONG where "
                                    + "username = ? and creation_date "
                                        + "= ?");
                                st.setString(1, uname);
                                st.setString(2, date);
                                st.executeUpdate();
                                st = con.prepareStatement("delete from FANTASY_COM where "
                                    + "username = ? and creation_date "
                                        + "= ?");
                                st.setString(1, uname);
                                st.setString(2, date);
                                st.executeUpdate();
                                st = con.prepareStatement("delete from FANTASY_SHOW where "
                                    + "username = ? and creation_date "
                                        + "= ?");
                                st.setString(1, uname);
                                st.setString(2, date);
                                st.executeUpdate();
                            }
                            catch (java.sql.SQLException e)
                            {
                                System.out.print(e);
                                return;
                            }
                        }
                        
                    }
                    else{
                        if(achoice<alistMenu.getBackOption())
                            displayDetail(alistMenu.getOptionAt(achoice));
                    }
                    break;
                
                case (3):
                    if (userIsAdmin)
                    {
                        done = true;                        
                        break;       
                    }
                    Date date = new Date();
                    Timestamp time = new Timestamp(date.getTime());
                    try
                    {
                        PreparedStatement st = con.prepareStatement("insert into FANTASY_SHOW values (?,'"+time+"','"+time+"')");
                        st.setString(1, username);
                        st.executeUpdate();
                    }
                    catch (java.sql.SQLException e)
                    {
                        break;
                    }
                    String stime = time.toString();
                    addSongs(stime);
                    break;
                
                case (4):
                    if (userIsAdmin)
                    {
                        done = true;                        
                        break;       
                    }
                    
                    System.out.println();
                    
                    try
                    {
                        Statement stmt = con.createStatement(
                                            ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
                        ResultSet rs = stmt.executeQuery("select count(*),f.username,"
                                + "f.creation_date from Fantasy_song as f INNER JOIN"
                                + " Played_at as p on f.sname=p.sname group by "
                                + "f.username,f.creation_date order by count(*) DESC");
                        
                        if(rs.first())
                        {
                            String datetime = rs.getString("CREATION_DATE");
                            
                            String[] tokens = datetime.split(" ");
                            String cdate = tokens[0];
                            String ctime = tokens[1];
                            
                            
                            System.out.println(rs.getString("USERNAME") + " has the most accurate prediction.");
                            System.out.println("Their fantasy show was created on " + cdate + " at "
                                    + ctime);
                            System.out.println("Songs on this fantasy show have been played live "+rs.getString(1) +" times.");
                        }
                        else
                        {
                            System.out.println("No fantasy shows match any real concert!");
                        }
                        
                    }
                    catch (java.sql.SQLException e)
                    {
                        System.out.print(e);
                        return;
                    }
                    
                    break;
                           
                
                default:
                    done = true;
                    break;
            }
        }
    }
    
    public static void displayDetail(String selection)
    {
        try
        {
            String[] tokens = selection.split(" ");
            String uname = tokens[0];
            String date = tokens[1]+ " " + tokens[2];
            
            PreparedStatement stmt = con.prepareStatement("select last_edit from FANTASY_SHOW where Username=? AND Creation_date='"+date+"'",
                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_READ_ONLY);
            stmt.setString(1, uname);
            ResultSet rs = stmt.executeQuery();


            rs.first();
            
            System.out.println();     
            System.out.printf("Last Edit: %s\n\n",rs.getTimestamp("last_edit").toString());
            
            stmt = con.prepareStatement("select sname from FANTASY_SHOW "
                    + "natural join FANTASY_SONG where Username = ? AND"
                    + " Creation_date = '"+date+"'",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            stmt.setString(1, uname);
            ResultSet rs2 = stmt.executeQuery();
            
            
            if(rs2.first()){          

                System.out.println("Set List:");
                do
                {
                    System.out.println(rs2.getString(1));
                } while (rs2.next());
            }
            else
                System.out.println("Set List is Empty");

            System.out.println();

            PreparedStatement stmt3 = con.prepareStatement("select commenter, comment from fantasy_com"
                                    + " where username = ? AND creation_date = '" 
                                    + date + "'",
                                            ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
            stmt3.setString(1, uname);
            ResultSet rs3 = stmt3.executeQuery();


            if(rs3.first()){

                System.out.println("Comments");

                do{


                    System.out.println(rs3.getString("COMMENTER") + ": " + rs3.getString("COMMENT"));


                }while(rs3.next());
            }
            else
                System.out.println("No Comments");

            if(!userIsAdmin)
            {
                boolean allDone = false;
                while(!allDone){
                        
                    Menu comMenu = new Menu();
                    comMenu.addOption("Add Comment");
                    comMenu.addOption("Find Closest Match");

                    if(username.equals(uname))
                    {
                        comMenu.addOption("Edit Set List");
                    }

                    Scanner input = new Scanner(System.in);
                    switch(comMenu.display()){

                    case(1): 
                        System.out.print(username + ": ");
                        String comment = input.nextLine();

                        PreparedStatement st = con.prepareStatement("insert into FANTASY_COM values (?,'"+date+"',?,?)");
                        st.setString(1, uname);
                        st.setString(2, username);
                        st.setString(3, comment);
                        st.executeUpdate();
                        break;

                    case(2):
                        PreparedStatement st4 = con.prepareStatement("select count(*),p.address,"
                                + "p.CITY,p.ST,p.BNAME,p.PDATE from Fantasy_song "
                                + "as f INNER JOIN Played_at as p on f.sname=p.sname"
                                + " where username = ? and creation_date "
                                + "= '"+date+"' group by p.address,"
                                + "p.city,p.st,p.bname,p.pdate order by count(*) DESC",
                                                ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                ResultSet.CONCUR_READ_ONLY);
                        st4.setString(1, uname);
                        ResultSet rs4 = st4.executeQuery();

                        if(rs4.first())
                        {
                            PreparedStatement st5 = con.prepareStatement("select vname from venue"
                                    + " where address = ? and city = ? and st = ?",
                                                ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                ResultSet.CONCUR_READ_ONLY);
                            st5.setString(1, rs4.getString("ADDRESS"));
                            st5.setString(2, rs4.getString("CITY"));
                            st5.setString(3, rs4.getString("ST"));
                            ResultSet rs5 = st5.executeQuery();

                            rs5.first();

                            System.out.println("This Fantasy Show is most similar to the actual Performance:");
                            System.out.println(rs4.getString("BNAME")+" at "+
                                    rs5.getString(1) + " on " + rs4.getString("PDATE"));
                        }
                        else
                        {
                            System.out.println("\nNo match found!");
                        }
                        break;

                    case(3):
                        if(!username.equals(uname))
                        {
                            break;
                        }

                        boolean done = false;

                        while(!done){
                            Menu edit = new Menu();
                            edit.addQuitOption();
                            edit.removeBackOption();
                            edit.addOption("Add song");
                            edit.addOption("Delete song");

                            ResultSet setList= stmt.executeQuery("select sname from FANTASY_SHOW "
                                            + "natural join FANTASY_SONG where Username = '"+uname+"' AND"
                                            + " Creation_date = '"+date+"'");

                            System.out.println();

                            if(setList.first())
                            {
                                System.out.println("Set List:");

                                do{

                                    System.out.println(setList.getString(1));

                                }while(setList.next());
                            }
                            else
                            {
                                System.out.println("Set List is Empty.");
                            }

                            switch(edit.display()){
                                case(1):
                                    addSongs(date);
                                    break;

                                case(2):
                                    Menu delete = new Menu();
                                    delete.setTitle("Select Song To Delete:");


                                    if(setList.first()){
                                        do{

                                            delete.addOption(setList.getString(1));

                                        }while(setList.next());

                                        int del = delete.display();
                                        if(del<delete.getBackOption()){

                                            setList.absolute(del);

                                            PreparedStatement newSt = con.prepareStatement("delete from"
                                                    + " fantasy_song where sname = ?"
                                                    + " and username = ? and"
                                                    + " creation_date = '"+date+"'",
                                                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                    ResultSet.CONCUR_READ_ONLY);
                                            newSt.setString(1, setList.getString(1));
                                            newSt.setString(2, uname);
                                            newSt.executeUpdate();

                                            Date now = new Date();
                                            Timestamp time = new Timestamp(now.getTime());
                                            newSt = con.prepareStatement("update fantasy_show set last_edit ="
                                                    + " '"+time+"' where username = ? and creation_date = '"
                                                    +date+"'");
                                            newSt.setString(1, uname);
                                            newSt.executeUpdate();
                                        }
                                    }
                                    else
                                    {
                                        System.out.println("\nNo Songs Exist To Delete.");
                                    }

                                    break;

                                default:
                                    done = true;
                                    break;
                            }
                        }
                        break;

                    default:
                        allDone = true;
                        break;
                    }
                }
            }    
        }
        catch (java.sql.SQLException e)
        {
            System.out.print(e);
            return;
        }
        
    }
    
    public static void addSongs(String time)
    {
        boolean more = true;
        while(more=true){
            Menu newMenu = new Menu();
            newMenu.setTitle("Select a Song To Add To Fantasy List: ");
            newMenu.addQuitOption();
            newMenu.removeBackOption();
            newMenu.addOptionsFromSQLQuery(con, "select sname from song as s where "
                    + "not exists(select * from fantasy_song as f where f.sname=s.sname "
                    + "and username = '"+username+"' and creation_date = '"+time+"')");

            int songChoice = newMenu.display();
            String song = newMenu.getOptionAt(songChoice);
            if(songChoice < newMenu.getQuitOption()){
                try
                {
                    PreparedStatement st = con.prepareStatement("insert into FANTASY_SONG values (?,?,'"+time+"')");
                    st.setString(1, song);
                    st.setString(2, username);
                    st.executeUpdate();
                    
                    Date now = new Date();
                    Timestamp rightNow = new Timestamp(now.getTime());
                    st = con.prepareStatement("update fantasy_show set last_edit ="
                            + " '"+rightNow+"' where username = ? and creation_date = '"
                            +time+"'");
                    st.setString(1, username);
                    st.executeUpdate();
                }
                catch (java.sql.SQLException e)
                {
                    System.out.println("SQL failed in add song." + e);

                }
            }
            else{
                more = false;
                return;
            }
        }
        return;
    }
}
